﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DataBaseManager
{
	public partial class Form1 : Form
	{
		#region 전역영역

		private static string gsServer = "";
		private static string gsUserId = "";
		private static string gsPassword = "";
		private static string gsConnStr = "";
		
		#endregion
		
		public Form1()
		{
			InitializeComponent();
		}

		#region 폼 로드
		
		//폼 로드시
		private void Form1_Load(object sender, EventArgs e)
		{
			gBoxBackup.Enabled = false;
			gBoxDrop.Enabled = false;
			gBoxRestore.Enabled = false;
		}
		
		#endregion

		#region 접속
		
		private void DbConnect()
		{
			gsServer = txtServer.Text;
			gsUserId = txtUserId.Text;
			gsPassword = txtPassword.Text;

			gsConnStr = "server=" + gsServer + ";database=master;user id=" + gsUserId + ";password=" + gsPassword;

			SqlConnection DBCon = new SqlConnection(gsConnStr);
			string lsSql = "select [name] AS DB from sys.databases where [name] NOT IN ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')";

			SqlCommand Cmd = new SqlCommand(lsSql);

			try
			{
				DBCon.Open();
				Cmd.Connection = DBCon;
				Cmd.ExecuteNonQuery();

				SqlDataReader Rd = Cmd.ExecuteReader();

				//초기화
				cBoxBackUp.Items.Clear();
				cBoxDrop.Items.Clear();

				while (Rd.Read())
				{
					cBoxBackUp.Items.Add(Rd["DB"]);
					cBoxDrop.Items.Add(Rd["DB"]);
				}

				gBoxBackup.Enabled = true;
				gBoxDrop.Enabled = true;
				gBoxRestore.Enabled = true;

				//DB가 있으면 버튼 활성화
				btnBackup.Enabled = (cBoxBackUp.Items.Count > 0);
				btnDrop.Enabled = (cBoxDrop.Items.Count > 0);
				btnRestore.Enabled = false;

				txtLog.Text += "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] DB접속성공\r\n";

				Rd.Close();
			}
			catch (Exception E)
			{
				txtLog.Text += E.Message + "\r\n";
			}
			finally
			{
				DBCon.Close();
			}
		}

		//비밀번호창에서 엔터치면
		private void txtPassword_KeyDown(object sender, KeyEventArgs e)
		{
			if (e.KeyValue.Equals(13))
			{
				DbConnect();
			}
		}
		
		//접속버튼 클릭시
		private void btnConnect_Click(object sender, EventArgs e)
		{
			DbConnect();
		}
		
		#endregion

		#region 백업

		//DB 백업
		private void btnBackUp_Click(object sender, EventArgs e)
		{
			if (cBoxBackUp.Text.Length > 0)
			{
				//저장경로
				string lsBackUpPath = "C:\\Temp\\" + cBoxBackUp.Text + "_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".bak";

				//DB백업쿼리
				string lsSql
				= "BACKUP DATABASE " + cBoxBackUp.Text + " TO DISK = '" + lsBackUpPath + "' WITH FORMAT, NAME = 'Full Backup of " + cBoxBackUp.Text + "'";

				if (ExecSql(lsSql))
				{
					txtLog.Text += "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] DB백업성공\r\n";
				}
				else
				{
					txtLog.Text += "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] DB백업실패\r\n";
				}
			}
			else
			{
				txtLog.Text += "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] 백업할 데이터베이스를 선택하세요\r\n";
			}
		}

		#endregion

		#region 삭제

		//DB 삭제
		private void btnDrop_Click(object sender, EventArgs e)
		{
			if (cBoxDrop.Text.Length > 0)
			{
				//DB드랍쿼리
				string lsSql = "DROP DATABASE " + cBoxDrop.Text;

				if (ExecSql(lsSql))
				{
					txtLog.Text += "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] DB삭제성공\r\n";
				}
				else
				{
					txtLog.Text += "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] DB삭제실패\r\n";
				}
			}
			else
			{
				txtLog.Text += "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] 삭제할 데이터베이스를 선택하세요\r\n";
			}
		}
		
		#endregion

		#region 복원

		//복원할 백업파일 선택창
		private void btnOpenFilePath_Click(object sender, EventArgs e)
		{
			openFileDialog1.ShowDialog();
			txtRestoreFilePath.Text = openFileDialog1.FileName.ToString();

			//복원버튼 활성화
			btnRestore.Enabled = (openFileDialog1.FileName.Contains("UPDMS")) ? true : false;
		}

		//DB 복원
		private void btnRestore_Click(object sender, EventArgs e)
		{
			string lsBackupFilePath = txtRestoreFilePath.Text;
			btnRestore.Enabled = false;
			
			//DB복원쿼리
			string lsSql = "RESTORE DATABASE UPDMS  FROM DISK = '" + lsBackupFilePath + "' WITH RECOVERY, REPLACE";
			
			try
			{
				if (ExecSql(lsSql))
				{
					txtLog.Text += "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] DB복원성공\r\n";
				}
				else
				{
					txtLog.Text += "[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "] DB복원실패\r\n";
				}
			}
			catch(Exception E)
			{
				txtLog.Text += E.Message + "\r\n";
			}
		}	
		
		#endregion	
		
		#region 실행
		
		//실행
		private bool ExecSql(string psSql)
		{
			bool lbRslt = true;
			SqlConnection DBCon = new SqlConnection(gsConnStr);
			SqlCommand Cmd = new SqlCommand(psSql);

			try
			{
				DBCon.Open();
				Cmd.Connection = DBCon;
				Cmd.ExecuteNonQuery();
			}
			catch (Exception E)
			{
				lbRslt = false;
				txtLog.Text += E.Message+"\r\n";
			}
			finally
			{
				DBCon.Close();
			}

			return lbRslt;
		}
		
		#endregion			
		
	}
}